import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.*;
import java.util.regex.*;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import ch.ethz.ssh2.Connection;
import ch.ethz.ssh2.Session;
import ch.ethz.ssh2.StreamGobbler;

class Server
{
	private static boolean assign_default(String username, String password, String domain_name)
	{
		try
		{
			String[] cmd_elements = {"scp", "/var/lib/tomcat6/webapps/cptr562-2010-01/files/default_presentation.pptx", username + "@" + domain_name + ":C:/Documents and Settings/" + username + "/"};
			Process prcs = Runtime.getRuntime().exec(cmd_elements);
			InputStream cmd_output = prcs.getInputStream();
		

			Connection conn = new Connection(domain_name);
			conn.connect();

			boolean isAuthenticated = conn.authenticateWithPassword(username, password);

			if (isAuthenticated == false)
				throw new IOException("Authentication failed.");

			Session sess = conn.openSession();

			sess.execCommand("cmd.exe");
			InputStream stdout = new StreamGobbler(sess.getStdout());

			sess.execCommand("powwer point command");
			stdout = new StreamGobbler(sess.getStdout());

			BufferedReader br = new BufferedReader(new InputStreamReader(stdout));

			sess.close();
			conn.close();

		}
		catch (Exception e)
		{
			e.printStackTrace(System.err);
			return false;
		}
		return true;
	}


	private static boolean assign(String username, String password, String domain_name, String file_name)
	{
		try
		{
			String[] cmd_elements = {"scp", file_name, username + "@" + domain_name + ":C:/Documents and Settings/" + username + "/"};
			Process prcs = Runtime.getRuntime().exec(cmd_elements);
			InputStream cmd_output = prcs.getInputStream();
		

			Connection conn = new Connection(domain_name);
			conn.connect();

			boolean isAuthenticated = conn.authenticateWithPassword(username, password);

			if (isAuthenticated == false)
				throw new IOException("Authentication failed.");

			Session sess = conn.openSession();

			sess.execCommand("cmd.exe");
			InputStream stdout = new StreamGobbler(sess.getStdout());

			sess.execCommand("powwer point command");
			stdout = new StreamGobbler(sess.getStdout());

			BufferedReader br = new BufferedReader(new InputStreamReader(stdout));

			sess.close();
			conn.close();

		}
		catch (Exception e)
		{
			e.printStackTrace(System.err);
			return false;
		}
		return true;
	}

	public static void main(String[] args) 
	{
		System.out.println("Test"); 

		try
		{
			// update the database
			String DRIVER = "com.mysql.jdbc.Driver";
			Class.forName(DRIVER);
			java.sql.Connection con=null;
			ResultSet rst=null;
						
			String url="jdbc:mysql://ecstiger.cs.andrews.edu/d562_2010_01?user=u562_2010_01&password=YPJ8f4We";	
			con=DriverManager.getConnection(url);

			PreparedStatement stmt = con.prepareStatement("SELECT * FROM screen;");
			rst = stmt.executeQuery();

			while(rst.next())
			{
				//iterate over the set of screens so that every screen will get a presentation even if it is blank	

				// before a screen can be loaded with a presentation
				// the presentation must exist
				// the user that owns that presentation must have control of that screen
				// the presentation must be approved
				// else the screen is loaded with the next presentation or an empty presentation
				
				ResultSet rst2=null;

				stmt = con.prepareStatement("SELECT screen.id, screen.domain_name, screen.current_presentation, screen.user_name, screen.password, presentation.id, presentation.file_name, user.username FROM screen, screen_presentations, presentation, user WHERE screen.id = screen_presentations.screen_id AND screen_presentations.presentation_id = presentation.id AND presentation.user_id = user.id AND screen.id = ? ORDER BY presentation.id ASC;");
				stmt.setString(1, rst.getString(1));
				rst2 = stmt.executeQuery();
				
				// Move to the end of the result set 
				rst2.last(); 

				// Get the row number of the last row which is also the row count 
				int rowCount = rst2.getRow();
			
				//go to the first row
				rst2.first();
				 
				if(rowCount == 0)
				{
					//action if the screen does not have a presentation on it
				
					System.out.println("rowCount = 0");

					assign_default(rst2.getString(4), rst2.getString(5), rst2.getString(2));


				}
				else if(rowCount == 1)
				{
					//action if there is only one presentation on the screen
					
					System.out.println("rowCount = 1");
			
					assign(rst2.getString(4), rst2.getString(5), rst2.getString(2), "/var/lib/tomcat6/webapps/cptr562-2010-01/files/" + rst.getString(8) + "/" + rst2.getString(7));


				}
				else
				{
					// action if there is more than one presentation on the screen
					// first try to select a presentation whose id is more than the id of the current presentation
					// if that fails, then select the presentation with the lowest id in the list
					
					System.out.println("rowCount = many");
					
					boolean assigned = false;
					
					while(rst2.next())
					{
						if(rst.getInt(4) >= rst2.getInt(6))
						{			
							//do nothing 
						}	
						else
						{
							assign(rst2.getString(4), rst2.getString(5), rst2.getString(2), "/var/lib/tomcat6/webapps/cptr562-2010-01/files/" + rst.getString(8) + "/" + rst2.getString(7));							assigned = true;
							assigned = true;
							break;							
						}
					}	
					if(!assigned)
					{
						rst2.first();
						assign(rst2.getString(4), rst2.getString(5), rst2.getString(2), "/var/lib/tomcat6/webapps/cptr562-2010-01/files/" + rst.getString(8) + "/" + rst2.getString(7));					}
				}
			}

			stmt.close();
			con.close();
		}
		catch(Exception e)
		{
			System.out.println(e);
		}
	}
}



















